package cn.edu.zhku.citrusmonitor.baseinfo.technology.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.edu.zhku.citrusmonitor.domain.Bean;
import cn.edu.zhku.citrusmonitor.domain.TbTechnology;
import cn.edu.zhku.citrusmonitor.util.C3P0Util;
import cn.edu.zhku.citrusmonitor.util.ManagerThreadLocal;

public class TechnologyDaoImpl implements TechnologyDao{
	
	BeanProcessor bean = new GenerousBeanProcessor();
	RowProcessor processor = new BasicRowProcessor(bean);

	@Override
	public int insertTechnology(TbTechnology technology) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "";
		List<Object> list = new ArrayList<Object>(Arrays.asList(technology.getTechnologyId(), technology.getTitle(), technology.getIntroduction(),
				technology.getPublisher(), technology.getCategory(), technology.getFilePath()));
		if(technology.getPublishTime() != null) {
			list.add(technology.getPublishTime());
			sql = "insert into technology(technology_id,title,introduction,publisher,category,file_path,publish_time) values(?,?,?,?,?,?,?)";
			return qr.update(sql, list.toArray());
		}else {
			sql = "insert into technology(technology_id,title,introduction,publisher,category,file_path) values(?,?,?,?,?,?)";
			return qr.update(sql, list.toArray());
		}
	}

	@Override
	public int deleteTechnology(String technologyId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "delete from technology where technology_id=?";
		return qr.update(sql, technologyId);
	}

	@Override
	public int modifyTechnology(TbTechnology technology) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "update technology set title=?,introduction=?,publisher=?,category=?,publish_time=? where technology_id=?";
		return qr.update(sql, technology.getTitle(), technology.getIntroduction(), technology.getPublisher(),
				technology.getCategory(), technology.getPublishTime(), technology.getTechnologyId());
	}

	@Override
	public TbTechnology selectTechnologyById(String technologyId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select technology_id,title,introduction,publisher,category,file_path,publish_time,browse_count from technology where technology_id=?";
		return qr.query(sql , new BeanHandler<TbTechnology>(TbTechnology.class, processor), technologyId);
	}

	@Override
	public List<TbTechnology> selectTechnologyByCategory(String category) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select technology_id,title,introduction,publisher,category,file_path,publish_time,browse_count from technology where category=?";
		return qr.query(sql , new BeanListHandler<TbTechnology>(TbTechnology.class, processor), category);
	}

	@Override
	public String selectFilePathById(String technologyId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select file_path from technology where technology_id=?";
		return qr.query(sql, new ScalarHandler<>(1), technologyId);
	}

	@Override
	public int countList(Map<String, Object> condition) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		StringBuilder sql = new StringBuilder("select count(*) from technology where 1=1");
		List<Object> list = new ArrayList<Object>();
		if(condition == null || condition.size() == 0) {
			long count = qr.query(sql.toString(), new ScalarHandler<>(1));
			return (int)count;
		}else {
			// 遍历添加条件
			for(Map.Entry<String, Object> entry : condition.entrySet()) {
				sql.append(" and " + entry.getKey() + " like ?");
				list.add(entry.getValue());
			}
			long count = qr.query(sql.toString(), new ScalarHandler<>(1), list.toArray());
			return (int)count;
		}
	}

	@Override
	public List<TbTechnology> selectTechnologyList(int pageSize, int currentPage, Map<String, Object> condition) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		StringBuilder sql = new StringBuilder("select technology_id,title,introduction,publisher,category,publish_time,file_path,browse_count from technology where 1=1");
		List<Object> list = new ArrayList<Object>();
		if(condition == null || condition.size() == 0) {
			sql.append(" limit ?,?");
			list.add((currentPage-1)*pageSize);
			list.add(pageSize);
			return qr.query(sql.toString(), new BeanListHandler<TbTechnology>(TbTechnology.class, processor), list.toArray());
		}else {
			// 遍历添加条件
			for(Map.Entry<String, Object> entry : condition.entrySet()) {
				sql.append(" and " + entry.getKey() + " like ?");
				list.add("%"+entry.getValue()+"%");
			}
			sql.append(" limit ?,?");
			list.add((currentPage-1)*pageSize);
			list.add(pageSize);
			return qr.query(sql.toString(), new BeanListHandler<TbTechnology>(TbTechnology.class, processor), list.toArray());
		}
	}

	@Override
	public List<TbTechnology> selectTechnologyByCategoryLimit(int size, String category) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select technology_id,title,publish_time from technology where category=? order by publish_time desc limit 0,?";
		return qr.query(sql, new BeanListHandler<TbTechnology>(TbTechnology.class, processor), category, size);
	}

	@Override
	public int countList(String category) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select count(*) from technology where category=?";
		long count = qr.query(sql, new ScalarHandler<>(1), category);
		return (int)count;
	}

	@Override
	public List<Bean> selectTechnologyList(int pageSize, int currentPage, String category) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select technology_id as id, title, publish_time as time from technology where category=? order by publish_time desc limit ?,?";
		return qr.query(sql, new BeanListHandler<Bean>(Bean.class), category, (currentPage-1)*pageSize, pageSize);
	}

	@Override
	public Bean getTechnologyById(String technologyId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select technology_id as id,title,introduction as content,publisher as distribution, file_path as filePath,publish_time as time from technology where technology_id=?";
		return qr.query(sql , new BeanHandler<Bean>(Bean.class), technologyId);
	}
	
	@Override
	public int deleteTechnologysByIds(Object [][]params) throws SQLException {
		QueryRunner qr = new QueryRunner();
		String sql = "delete from technology where technology_id=?";
		int[] s = qr.batch(ManagerThreadLocal.getConnection(), sql, params);
		return s.length;
	}

}
